﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public class FSBase_DataAccess
    {
        private SqlConnection conn;

        #region 检索
        public DataTable Search(string sql)
        {
            try
            {
                DataSet ds = new DataSet();
                this.conn = Common.ComConnectionHelper.CreateSqlConnection_SPMS();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = this.conn;
                da.SelectCommand.CommandType = System.Data.CommandType.Text;
                da.SelectCommand.CommandText = sql;
                da.SelectCommand.CommandTimeout = 0;
                Common.ComConnectionHelper.OpenConection_SQL(ref this.conn);
                da.Fill(ds);
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                da.SelectCommand.Dispose();
                da.Dispose();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                throw ex;
            }
        }
        public DataSet SearchDs(string sql)
        {
            try
            {
                DataSet ds = new DataSet();
                this.conn = Common.ComConnectionHelper.CreateSqlConnection_SPMS();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = this.conn;
                da.SelectCommand.CommandType = System.Data.CommandType.Text;
                da.SelectCommand.CommandText = sql;
                da.SelectCommand.CommandTimeout = 0;
                Common.ComConnectionHelper.OpenConection_SQL(ref this.conn);
                da.Fill(ds);
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                da.SelectCommand.Dispose();
                da.Dispose();
                return ds;
            }
            catch (Exception ex)
            {
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                throw ex;
            }
        }
        #endregion

        #region 更新
        public void update(string sql)
        {
            SqlTransaction st = null;
            try
            {
                DataSet ds = new DataSet();
                this.conn = Common.ComConnectionHelper.CreateSqlConnection_SPMS();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = this.conn;
                da.SelectCommand.CommandTimeout = 0;
                da.SelectCommand.CommandType = System.Data.CommandType.Text;
                da.SelectCommand.CommandText = sql;
                da.SelectCommand.Connection.Open();
                st = this.conn.BeginTransaction();
                da.SelectCommand.Transaction = st;
                da.SelectCommand.ExecuteNonQuery();
                st.Commit();
                da.SelectCommand.Connection.Close();
                da.SelectCommand.Dispose();
                da.Dispose();
            }
            catch (Exception ex)
            {
                st.Rollback();
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                throw ex;
            }
        }

        public void update(string sql,Dictionary<string,object> Parameters)
        {
            SqlTransaction st = null;
            try
            {
                DataSet ds = new DataSet();
                this.conn = Common.ComConnectionHelper.CreateSqlConnection_SPMS();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = this.conn;
                da.SelectCommand.CommandTimeout = 0;
                da.SelectCommand.CommandType = System.Data.CommandType.Text;
                da.SelectCommand.CommandText = sql;
                foreach (var item in Parameters)
                {
                    da.SelectCommand.Parameters.Add(item.Key,item.Value);
                }
                da.SelectCommand.Connection.Open();
                st = this.conn.BeginTransaction();
                da.SelectCommand.Transaction = st;
                da.SelectCommand.ExecuteNonQuery();
                st.Commit();
                da.SelectCommand.Connection.Close();
                da.SelectCommand.Dispose();
                da.Dispose();
            }
            catch (Exception ex)
            {
                st.Rollback();
                Common.ComConnectionHelper.CloseConnection_SQL(ref this.conn);
                throw ex;
            }
        }

        #endregion
    }
}
